package com.xnx3.banmahuishou.superadmin.controller;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.TreeMap;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.bouncycastle.jcajce.provider.asymmetric.dsa.DSASigner.detDSA;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.xnx3.BaseVO;
import com.xnx3.DateUtil;
import com.xnx3.DoubleUtil;
import com.xnx3.banmahuishou.core.entity.Daishoudian;
import com.xnx3.banmahuishou.core.entity.MoneyChangeRecord;
import com.xnx3.banmahuishou.core.entity.Qishou;
import com.xnx3.banmahuishou.core.entity.QishouLeaveRecord;
import com.xnx3.banmahuishou.core.util.AppUtil;
import com.xnx3.banmahuishou.core.vo.HumanEffectAnalyzeVO;
import com.xnx3.banmahuishou.core.vo.OrderAnalyzeVO;
import com.xnx3.banmahuishou.core.vo.RiderAnalyzeListVO;
import com.xnx3.banmahuishou.core.vo.RiderAnalyzeVO;
import com.xnx3.banmahuishou.core.vo.WorkAnalyzeListVO;
import com.xnx3.banmahuishou.core.vo.WorkAnalyzePicVO;
import com.xnx3.banmahuishou.core.vo.WorkAnalyzeVO;
import com.xnx3.j2ee.controller.BaseController;
import com.xnx3.j2ee.entity.User;
import com.xnx3.j2ee.service.SqlService;
import com.xnx3.j2ee.util.ActionLogUtil;
import com.xnx3.j2ee.util.Page;
import com.xnx3.j2ee.util.Sql;
import com.xnx3.wangmarket.shop.core.entity.Order;

import org.apache.commons.lang.ObjectUtils;
import org.apache.jasper.tagplugins.jstl.core.If;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.bind.annotation.RestController;

/**
 * 总管理后台导出execl
 * @author 王晓龙
 */
@RestController(value="SuperadminDocController")
@RequestMapping("/superadmin/outdoc/")
public class OutDocController extends BaseController {

	@Resource
	private SqlService sqlService;

	/**
	 * 总后台导出订单管理execl
	 */
	@RequestMapping("getOrderExecl.json")
	public void getOrderExecl(HttpServletRequest request, HttpServletResponse response,
			@RequestParam(value = "startTime", required = false, defaultValue = "0") int startTime,
			@RequestParam(value = "endTime", required = false, defaultValue = "0") int endTime) throws Exception {

		//创建Sql
		Sql sql = new Sql(request);
		//配置按某个字端搜索内容
		sql.setSearchColumn(new String[] {"qishou_name=","no","phone=","state="});
		sql.appendWhere("((reserve_end_time > " + DateUtil.timeForUnix10() + " AND state = " + Order.STATE_DAIQUHUO + ") OR state = " + Order.STATE_FINISH + " OR state = " + Order.STATE_CANCEL + " OR state = "+ Order.STATE_EVALUATED + ")");
		//选择排序方式 当用户没有选择排序方式时，系统默认降序排序
		sql.setDefaultOrderBy("id DESC");

		if (startTime > 0) {
			sql.appendWhere(" addtime >= " + startTime);
		}
		if (endTime > 0) {
			//传入指定的时间返回明天00:00:00的时间戳
			int dayEndTime = DateUtil.getDateZeroTime(endTime)+24*60*60;
			sql.appendWhere(" addtime <= " + dayEndTime);
		}
		String string ="SELECT * FROM shop_order" + sql.getSql();
		// 查询订单记录
		List<Order> list = sqlService.findBySqlQuery(string, Order.class);

		if (list == null || list.size() < 0) {
			return;
		}
		// 准备下载的文件名
		String filename = "OrderExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("订单数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"订单号","出售人姓名","出售人手机号","骑手姓名","骑手电话","所属网格名称","所属代理商名称","服务状态","预计金额(元)","实付金额(元)","总重量(kg)","备注","下单时间","订单评分","订单评语"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 15; j++) {
			sheet.setColumnWidth(j,26*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= list.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);
			for (int j = 0; j < 15; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(list.get(i-1).getNo());
					break;
				case 1:
					cell.setCellValue(list.get(i-1).getUsername());
					break;
				case 2:
					cell.setCellValue(list.get(i-1).getPhone());
					break;
				case 3:
					cell.setCellValue(list.get(i-1).getQishouName());
					break;
				case 4:
					cell.setCellValue(list.get(i-1).getQishouPhone());
					break;
				case 5:
					cell.setCellValue(list.get(i-1).getWanggeUsername());
					break;
				case 6:
					cell.setCellValue(list.get(i-1).getDailishangUsername());
					break;
				case 7:
					cell.setCellValue(Integer.parseInt(list.get(i-1).getState().toString()) == 1 ? "待接单" :Integer.parseInt(list.get(i-1).getState().toString()) == 2 ? "待取货" : Integer.parseInt(list.get(i-1).getState().toString()) == 3 ? "已完成，待评价" : Integer.parseInt(list.get(i-1).getState().toString()) == 4 ? "已取消" : "已完成，已评价");
					break;
				case 8:
					cell.setCellValue((double)list.get(i-1).getTotalMoney()/100);
					break;
				case 9:
					cell.setCellValue((double)list.get(i-1).getPayMoney()/100);
					break;
				case 10:
					cell.setCellValue((double)list.get(i-1).getAllWeight()/1000);
					break;
				case 11:
					cell.setCellValue(list.get(i-1).getRemark());
					break;
				case 12:
					cell.setCellValue(DateUtil.dateFormat(list.get(i-1).getAddtime(),"yyyy-MM-dd HH:mm"));
					break;
				case 13:
					if(list.get(i-1).getEvaluationScore() != null ) {
						int evaluationScore = list.get(i-1).getEvaluationScore();
						double score = DoubleUtil.doubleSplit((double)evaluationScore / 10.0, 1);
						cell.setCellValue(score);
					}else {
						cell.setCellValue(0);
					}
					break;
				case 14:
					cell.setCellValue(list.get(i-1).getOrderEvaluation());
					break;
				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出订单管理execl");
		return;
	}

	/**
	 * 总后台导出异常订单execl
	 */
	@RequestMapping("getNotTakenListExecl.json")
	public void getNotTakenListExecl(HttpServletRequest request, HttpServletResponse response) throws Exception {
		//创建Sql
		Sql sql = new Sql(request);
		//配置按某个字端搜索内容
		sql.setSearchColumn(new String[] {"qishou_name="});
		// 状态是待接单  或者 状态是待取货 且 已超过预约时间
		sql.appendWhere("((reserve_end_time < " + DateUtil.timeForUnix10() + " AND state = " + Order.STATE_DAIQUHUO + ") OR state = " + Order.STATE_DAIJIEDAN + ")");
		//选择排序方式 当用户没有选择排序方式时，系统默认降序排序
		sql.setDefaultOrderBy("id DESC");
		String string ="SELECT * FROM shop_order" + sql.getSql();
		// 查询订单记录
		List<Order> list = sqlService.findBySqlQuery(string, Order.class);

		if (list == null || list.size() < 0) {
			return;
		}
		// 准备下载的文件名
		String filename = "NotTakenListExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("异常订单数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"订单号","预约时间","出售人姓名","出售人手机号","骑手姓名","骑手电话","所属网格名称","所属代理商名称","服务状态","备注"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 10; j++) {
			sheet.setColumnWidth(j,26*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= list.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 10; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(list.get(i-1).getNo());
					break;
				case 1:
					cell.setCellValue(DateUtil.dateFormat(list.get(i-1).getReserveStartTime(),"yyyy-MM-dd HH:mm") + "~" + DateUtil.dateFormat(list.get(i-1).getReserveEndTime(),"HH:mm"));
					break;
				case 2:
					cell.setCellValue(list.get(i-1).getUsername());
					break;
				case 3:
					cell.setCellValue(list.get(i-1).getPhone());
					break;
				case 4:
					cell.setCellValue(list.get(i-1).getQishouName());
					break;
				case 5:
					cell.setCellValue(list.get(i-1).getQishouPhone());
					break;
				case 6:
					cell.setCellValue(list.get(i-1).getWanggeUsername());
					break;
				case 7:
					cell.setCellValue(list.get(i-1).getDailishangUsername());
					break;
				case 8:
					cell.setCellValue(Integer.parseInt(list.get(i-1).getState().toString()) == 1 ? "待接单" :Integer.parseInt(list.get(i-1).getState().toString()) == 2 ? "待取货" : Integer.parseInt(list.get(i-1).getState().toString()) == 3 ? "已完成，待评价" : Integer.parseInt(list.get(i-1).getState().toString()) == 4 ? "已取消" : "已完成，已评价");
					break;
				case 9:
					cell.setCellValue(list.get(i-1).getRemark());
					break;

				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出异常订单execl");
		return;
	}

	
	
	/**
	 * 总后台导出骑手管理execl
	 */
	@RequestMapping("getQishouExecl.json")
	public void getQishouExecl(HttpServletRequest request, HttpServletResponse response) throws Exception {

		//创建Sql
		Sql sql = new Sql(request);
		//配置按某个字端搜索内容
		sql.setSearchColumn(new String[] {"wangge_username=","dailishang_username=","phone=","state="});

		String string ="SELECT * FROM laji_qishou" + sql.getSql();
		// 查询骑手记录
		List<Qishou> list = sqlService.findBySqlQuery(string, Qishou.class);

		if (list == null || list.size() < 0) {
			return;
		}
		// 准备下载的文件名
		String filename = "QishouExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("骑手数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"代理商名称","网格名称","骑手姓名","骑手电话","注册时间","服务次数","工作状态","服务范围","总回收重量(kg)","总支出","每天限额(元)","每单限额(元)"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 12; j++) {
			sheet.setColumnWidth(j,26*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= list.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 12; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(list.get(i-1).getDailishangUsername());
					break;
				case 1:
					cell.setCellValue(list.get(i-1).getWanggeUsername());
					break;
				case 2:
					cell.setCellValue(list.get(i-1).getName());
					break;
				case 3:
					cell.setCellValue(list.get(i-1).getPhone());
					break;
				case 4:
					cell.setCellValue(DateUtil.dateFormat(list.get(i-1).getAddtime(),"yyyy-MM-dd HH:mm"));
					break;
				case 5:
					cell.setCellValue(list.get(i-1).getAllServiceNumb());
					break;
				case 6:
					cell.setCellValue(list.get(i-1).getState() == 1 ? "正常" : "请假" );
					break;
				case 7:
					cell.setCellValue(list.get(i-1).getServiceArea());
					break;
				case 8:
					cell.setCellValue((double)list.get(i-1).getAllWgight()/1000);
					break;
				case 9:
					cell.setCellValue((double)list.get(i-1).getAllExpend()/100);
					break;
				case 10:
					cell.setCellValue((double)list.get(i-1).getDayQuota()/100);
					break;
				case 11:
					cell.setCellValue((double)list.get(i-1).getDanQuota()/100);
					break;

				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出骑手管理execl");
		return;
	}

	/**
	 * 总后台导出用户管理execl
	 */
	@RequestMapping("getUserExecl.json")
	public void getUserExecl(HttpServletRequest request, HttpServletResponse response) throws Exception {

		// 查询语句
		String	string = "SELECT * FROM user WHERE authority = 11";

		// 查询用户记录
		List<User> list = sqlService.findBySqlQuery(string, User.class);

		if (list == null || list.size() < 0) {
			return;
		}
		// 准备下载的文件名
		String filename = "UserExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("用户数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"用户等级","用户昵称","用户手机号","下单数","总重量(kg)","总金额(元)","注册时间"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 7; j++) {
			sheet.setColumnWidth(j,26*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= list.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 7; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(list.get(i-1).getGrade());
					break;
				case 1:
					cell.setCellValue(list.get(i-1).getNickname());
					break;
				case 2:
					cell.setCellValue(list.get(i-1).getPhone());
					break;
				case 3:
					cell.setCellValue(list.get(i-1).getAllOrderNumbe());
					break;
				case 4:
					cell.setCellValue((double)list.get(i-1).getAllSellWgight()/1000);
					break;
				case 5:
					cell.setCellValue((double)list.get(i-1).getAllIncomeMone()/100);
					break;
				case 6:
					cell.setCellValue(DateUtil.dateFormat(list.get(i-1).getRegtime(),"yyyy-MM-dd HH:mm"));
					break;

				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出用户管理execl");
		return;
	}

	/**
	 * 导出指定代理商账户流水execl
	 */
	@RequestMapping("getAccountExecl.json")
	public void getAccountExecl(HttpServletRequest request, HttpServletResponse response,
			@RequestParam(value = "userid",required = true,defaultValue = "0") int userid) throws Exception {
		// 查询语句
		String	string = "SELECT * FROM laji_money_change_record WHERE user_id = "+ userid;

		// 查询账户流水记录
		List<MoneyChangeRecord> list = sqlService.findBySqlQuery(string, MoneyChangeRecord.class);

		if (list == null || list.size() < 0) {
			return;
		}
		// 准备下载的文件名
		String filename = "AccountExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("账户流水数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"代理商名称","代理商手机号","金额变动数目(元)","金额变动时间","金额变动描述"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 5; j++) {
			sheet.setColumnWidth(j,55*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= list.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 5; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(list.get(i-1).getUsername());
					break;
				case 1:
					cell.setCellValue(list.get(i-1).getPhone());
					break;
				case 2:
					cell.setCellValue((double)list.get(i-1).getMoney()/100);
					break;
				case 3:
					cell.setCellValue(DateUtil.dateFormat(list.get(i-1).getCreateTime(),"yyyy-MM-dd HH:mm"));
					break;
				case 4:
					cell.setCellValue(list.get(i-1).getRemark());
					break;
				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总管理后台导出指定代理商账户流水execl");
		return;
	}

	/**
	 * 总后台导出代收点管理execl
	 */
	@RequestMapping("getDaishoudianExecl.json")
	public void getDaishoudianExecl(HttpServletRequest request, HttpServletResponse response) throws Exception {

		//创建Sql
		Sql sql = new Sql(request);
		//配置按某个字端搜索内容
		sql.setSearchColumn(new String[] {"daishoudian_name=","state="});
		sql.setDefaultOrderBy("laji_daishoudian.state ASC");
		String string ="SELECT * FROM laji_daishoudian " + sql.getSql();
		// 查询骑手记录
		List<Daishoudian> list = sqlService.findBySqlQuery(string, Daishoudian.class);

		if (list == null || list.size() < 0) {
			return;
		}
		// 准备下载的文件名
		String filename = "DaishoudianExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("代收点数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"代收点名称","代收点地区","代收点详细地址","代收点联系人","代收点联系电话","骑手姓名","骑手手机号","经纬度","注册时间","状态","备注","驳回原因"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 12; j++) {
			sheet.setColumnWidth(j,26*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= list.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 12; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(list.get(i-1).getDaishoudianName());
					break;
				case 1:
					cell.setCellValue(list.get(i-1).getSheng()+list.get(i-1).getShi()+list.get(i-1).getQu());
					break;
				case 2:
					cell.setCellValue(list.get(i-1).getDaishoudianAddress());
					break;
				case 3:
					cell.setCellValue(list.get(i-1).getDaishoudianUsername());
					break;
				case 4:
					cell.setCellValue(list.get(i-1).getDaishoudianPhone());
					break;
				case 5:
					cell.setCellValue(list.get(i-1).getQishouUsername());
					break;
				case 6:
					cell.setCellValue(list.get(i-1).getQishouPhone());
					break;
				case 7:
					cell.setCellValue(list.get(i-1).getLongitude()+","+list.get(i-1).getLatitude());
					break;
				case 8:
					cell.setCellValue(DateUtil.dateFormat(list.get(i-1).getCreateTime(),"yyyy-MM-dd HH:mm"));
					break;
				case 9:
					cell.setCellValue(Integer.parseInt(list.get(i-1).getState().toString()) == 1 ? "审核中" :Integer.parseInt(list.get(i-1).getState().toString()) == 2 ? "已通过" : Integer.parseInt(list.get(i-1).getState().toString()) == 3 ? "已驳回" : "已冻结");
					break;
				case 10:
					cell.setCellValue(list.get(i-1).getRemark());
					break;
				case 11:
					cell.setCellValue(list.get(i-1).getRefuseReason());
					break;

				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出代收点管理execl");
		return;
	}

	/**
	 * 总后台导出订单数据execl
	 */
	@RequestMapping("getOrderDataExecl.json")
	public void getOrderDataExecl(HttpServletRequest request, HttpServletResponse response,
			@RequestParam(value = "startTime", required = false, defaultValue = "0") int startTime,
			@RequestParam(value = "endTime", required = false, defaultValue = "0") int endTime) throws Exception {

		// 日期天数, 如果没有选择时间筛选条件 默认七天
		int day = 7;
		if (startTime > 0 && endTime > 0) {
			String startday = DateUtil.intToString(startTime, "yyyyMMdd");
			String endday = DateUtil.intToString(endTime, "yyyyMMdd");
			day = AppUtil.daysBetween(startday, endday, "yyyyMMdd");
			// 获取天数
			day = day + 1;
			endTime = endTime + 86400;
		}else {
			endTime = DateUtil.getDateZeroTime(DateUtil.timeForUnix10());// 当日零点的时间戳 
			startTime = endTime - 7 * 86400;// 一周之前时间戳
		}
		// 日期数组
		int[] dateI = new int[day];
		Integer firstday = Integer.valueOf(DateUtil.intToString(startTime, "yyyyMMdd"));
		dateI[0] = firstday;
		for (int j = 1; j < dateI.length; j++) {
			// 在上个元素天数的基础上加一天
			dateI[j] = dateI[j-1] + 1;
			// 当前日期
			String dateString = dateI[j] + "";
			// 判断当前日是否是当前月最后一日
			int monthLastDay = AppUtil.getMonthLastDay(DateUtil.StringToInt(dateI[j-1] + "", "yyyyMMdd"), "yyyyMMdd");
			if (dateI[j] - monthLastDay == 1) {
				// 判断当前月是否是当前年最后一月
				if (Objects.equals(dateString.substring(4, 6), "12")) {
					// 跨年跨月
					Integer substring = Integer.valueOf(dateString.substring(0, 4));
					String yearFirst = AppUtil.getYearFirst(substring + 1,"yyyyMMdd");
					dateI[j] = Integer.valueOf(yearFirst);
				}else {
					// 只跨月不跨年
					String firstDayOfNextMonth = AppUtil.getFirstDayOfNextMonth(dateI[j-1] + "", "yyyyMMdd");
					dateI[j] = Integer.valueOf(firstDayOfNextMonth);
				}
			}
		}

		// 获取在这个时间段的订单
		String ordernow = "SELECT * FROM shop_order WHERE addtime > " + startTime + " AND  addtime < " + endTime;
		List<Order> list = sqlService.findBySqlQuery(ordernow, Order.class);

		// 日期-数据对象
		Map<Integer, OrderAnalyzeVO> map = new TreeMap<Integer, OrderAnalyzeVO>();

		for (Order order : list) {
			// 根据日期进行数据分发
			Integer orderday = Integer.valueOf(DateUtil.intToString(order.getAddtime(), "yyyyMMdd"));
			// 某天的数据,用于列表
			OrderAnalyzeVO orderAnalyzeVO = map.get(orderday);
			if (orderAnalyzeVO == null) {
				orderAnalyzeVO = new OrderAnalyzeVO();
				orderAnalyzeVO.setDingdanShuliang(1);
				orderAnalyzeVO.setJunjia(order.getPayMoney());
				orderAnalyzeVO.setJunzhong(order.getAllWeight());
				if (ObjectUtils.equals(order.getIsNew(), 1)) {
					// 新用户下单数
					orderAnalyzeVO.setNewUserNum(1);
					// 老用户下单数
					orderAnalyzeVO.setOldUserNum(0);
				}else {
					// 新用户下单数
					orderAnalyzeVO.setNewUserNum(0);
					// 老用户下单数
					orderAnalyzeVO.setOldUserNum(1);
				}
				if (ObjectUtils.equals(order.getState(), Order.STATE_FINISH) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED)) {
					// 完成量
					orderAnalyzeVO.setWanchengNum(1);
					// 取消量
					orderAnalyzeVO.setQuxiaoNum(0);
					// 异常量
					orderAnalyzeVO.setYichangNum(0);
					// 待取量
					orderAnalyzeVO.setDaiquNum(0);
				}else if (ObjectUtils.equals(order.getState(), Order.STATE_CANCEL)) {
					// 完成量
					orderAnalyzeVO.setWanchengNum(0);
					// 取消量
					orderAnalyzeVO.setQuxiaoNum(1);
					// 异常量
					orderAnalyzeVO.setYichangNum(0);
					// 待取量
					orderAnalyzeVO.setDaiquNum(0);
				}else if (ObjectUtils.equals(order.getState(), Order.STATE_DAIQUHUO) && order.getReserveEndTime() > DateUtil.timeForUnix10()) {
					// 完成量
					orderAnalyzeVO.setWanchengNum(0);
					// 取消量
					orderAnalyzeVO.setQuxiaoNum(0);
					// 异常量
					orderAnalyzeVO.setYichangNum(0);
					// 待取量
					orderAnalyzeVO.setDaiquNum(1);
				}else {
					// 完成量
					orderAnalyzeVO.setWanchengNum(0);
					// 取消量
					orderAnalyzeVO.setQuxiaoNum(0);
					// 异常量
					orderAnalyzeVO.setYichangNum(1);
					// 待取量
					orderAnalyzeVO.setDaiquNum(0);
				}
				// 日期
				orderAnalyzeVO.setRiqi(Integer.valueOf(DateUtil.intToString(order.getAddtime(), "yyyyMMdd")));
				// 添加到map
				map.put(orderday, orderAnalyzeVO);

			}else {
				orderAnalyzeVO.setDingdanShuliang(orderAnalyzeVO.getDingdanShuliang() + 1);
				orderAnalyzeVO.setJunjia(orderAnalyzeVO.getJunjia() + order.getPayMoney());
				orderAnalyzeVO.setJunzhong(orderAnalyzeVO.getJunzhong() + order.getAllWeight());
				if (ObjectUtils.equals(order.getIsNew(), 1)) {
					// 新用户下单数
					orderAnalyzeVO.setNewUserNum(orderAnalyzeVO.getNewUserNum() + 1);
				}else {
					// 老用户下单数
					orderAnalyzeVO.setOldUserNum(orderAnalyzeVO.getOldUserNum() + 1);
				}
				if (ObjectUtils.equals(order.getState(), Order.STATE_FINISH) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED)) {
					// 完成量
					orderAnalyzeVO.setWanchengNum(orderAnalyzeVO.getWanchengNum() + 1);
				}else if (ObjectUtils.equals(order.getState(), Order.STATE_CANCEL)) {
					// 取消量
					orderAnalyzeVO.setQuxiaoNum(orderAnalyzeVO.getQuxiaoNum() + 1);
				}else if (ObjectUtils.equals(order.getState(), Order.STATE_DAIQUHUO) && order.getReserveEndTime() > DateUtil.timeForUnix10()) {
					// 待取量
					orderAnalyzeVO.setDaiquNum(orderAnalyzeVO.getDaiquNum() + 1);
				}else {
					// 异常量
					orderAnalyzeVO.setYichangNum(orderAnalyzeVO.getYichangNum() + 1);
				}
				// 添加到map
				map.put(orderday, orderAnalyzeVO);
			}
		}

		// 根据日期补全数据
		for (int j = 0; j < dateI.length; j++) {
			OrderAnalyzeVO analyzeVO = map.get(dateI[j]);
			if (analyzeVO == null) {
				analyzeVO = new OrderAnalyzeVO(dateI[j], 0, 0, 0, 0, 0, 0, 0, 0, 0);
				map.put(dateI[j], analyzeVO);
			}
		}

		// 数据列表--返回列表使用
		List<OrderAnalyzeVO> analyzeVOs = new ArrayList<OrderAnalyzeVO>();
		map.forEach((k,v) ->{
			analyzeVOs.add(v);
		});

		// 准备下载的文件名
		String filename = "OrderDataExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("订单数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"日期","订单数量","新用户下单数","老用户下单数","完成量","取消量","异常量","待取量","完成订单均价(元)","总订单均重(kg)"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 10; j++) {
			sheet.setColumnWidth(j,26*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= analyzeVOs.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 10; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(analyzeVOs.get(i-1).getRiqi());
					break;
				case 1:
					cell.setCellValue(analyzeVOs.get(i-1).getDingdanShuliang());
					break;
				case 2:
					cell.setCellValue(analyzeVOs.get(i-1).getNewUserNum());
					break;
				case 3:
					cell.setCellValue(analyzeVOs.get(i-1).getOldUserNum());
					break;
				case 4:
					cell.setCellValue(analyzeVOs.get(i-1).getWanchengNum());
					break;
				case 5:
					cell.setCellValue(analyzeVOs.get(i-1).getQuxiaoNum());
					break;
				case 6:
					cell.setCellValue(analyzeVOs.get(i-1).getYichangNum());
					break;
				case 7:
					cell.setCellValue(analyzeVOs.get(i-1).getDaiquNum());
					break;
				case 8:
					//这里先是把分计算成元最后计算的是均价总金额/已完成订单数的值
					double junJia = (double)analyzeVOs.get(i-1).getJunjia()/100;
					if (analyzeVOs.get(i-1).getWanchengNum() > 0) {
						cell.setCellValue(DoubleUtil.doubleSplit((double)junJia/analyzeVOs.get(i-1).getWanchengNum(),2));
					}else {
						cell.setCellValue(DoubleUtil.doubleSplit((double)junJia,2));
					}

					break;
				case 9:
					//这里先是把g计算成kg最后计算的是均重量/总订单的数量的值
					double junZhong = (double)analyzeVOs.get(i-1).getJunzhong()/1000;
					if (analyzeVOs.get(i-1).getDingdanShuliang() > 0) {
						cell.setCellValue(DoubleUtil.doubleSplit((double)junZhong/analyzeVOs.get(i-1).getDingdanShuliang(),2));
					}else {
						cell.setCellValue(DoubleUtil.doubleSplit((double)junZhong,2));
					}

					break;
				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出订单数据execl");
		return;
	}

	/**
	 * 总后台导出用户数据execl
	 */
	@RequestMapping("getUserDataExecl.json")
	public void getUserDataExecl(HttpServletRequest request, HttpServletResponse response,
			@RequestParam(value = "startTime", required = false, defaultValue = "0") int startTime,
			@RequestParam(value = "endTime", required = false, defaultValue = "0") int endTime) throws Exception {

		// 日期天数, 如果没有选择时间筛选条件 默认七天
		int day = 7;
		if (startTime > 0 && endTime > 0) {
			String startday = DateUtil.intToString(startTime, "yyyyMMdd");
			String endday = DateUtil.intToString(endTime, "yyyyMMdd");
			day = AppUtil.daysBetween(startday, endday, "yyyyMMdd");
			// 获取天数
			day = day + 1;
			endTime = endTime + 86400;
		}else {
			endTime = DateUtil.getDateZeroTime(DateUtil.timeForUnix10());// 当日零点的时间戳 
			startTime = endTime - 7 * 86400;// 一周之前时间戳
		}
		// 日期数组
		int[] dateI = new int[day];
		Integer firstday = Integer.valueOf(DateUtil.intToString(startTime, "yyyyMMdd"));
		dateI[0] = firstday;
		for (int j = 1; j < dateI.length; j++) {
			// 在上个元素天数的基础上加一天
			dateI[j] = dateI[j-1] + 1;
			// 当前日期
			String dateString = dateI[j] + "";
			// 判断当前日是否是当前月最后一日
			int monthLastDay = AppUtil.getMonthLastDay(DateUtil.StringToInt(dateI[j-1] + "", "yyyyMMdd"), "yyyyMMdd");
			if (dateI[j] - monthLastDay == 1) {
				// 判断当前月是否是当前年最后一月
				if (Objects.equals(dateString.substring(4, 6), "12")) {
					// 跨年跨月
					Integer substring = Integer.valueOf(dateString.substring(0, 4));
					String yearFirst = AppUtil.getYearFirst(substring + 1,"yyyyMMdd");
					dateI[j] = Integer.valueOf(yearFirst);
				}else {
					// 只跨月不跨年
					String firstDayOfNextMonth = AppUtil.getFirstDayOfNextMonth(dateI[j-1] + "", "yyyyMMdd");
					dateI[j] = Integer.valueOf(firstDayOfNextMonth);
				}
			}
		}

		// 获取在这个时间段的订单
		String ordernow = "SELECT * FROM shop_order WHERE addtime > " + startTime + " AND  addtime < " + endTime;
		List<Order> list = sqlService.findBySqlQuery(ordernow, Order.class);

		// 日期-数据对象
		Map<Integer, OrderAnalyzeVO> map = new TreeMap<Integer, OrderAnalyzeVO>();

		for (Order order : list) {
			// 根据日期进行数据分发
			Integer orderday = Integer.valueOf(DateUtil.intToString(order.getAddtime(), "yyyyMMdd"));
			// 某天的数据,用于列表
			OrderAnalyzeVO orderAnalyzeVO = map.get(orderday);
			if (orderAnalyzeVO == null) {
				orderAnalyzeVO = new OrderAnalyzeVO();
				orderAnalyzeVO.setDingdanShuliang(1);
				if (ObjectUtils.equals(order.getIsNew(), 1)) {
					// 新用户下单数
					orderAnalyzeVO.setNewUserNum(1);
					// 老用户下单数
					orderAnalyzeVO.setOldUserNum(0);
				}else {
					// 新用户下单数
					orderAnalyzeVO.setNewUserNum(0);
					// 老用户下单数
					orderAnalyzeVO.setOldUserNum(1);
				}

				// 日期
				orderAnalyzeVO.setRiqi(Integer.valueOf(DateUtil.intToString(order.getAddtime(), "yyyyMMdd")));
				// 添加到map
				map.put(orderday, orderAnalyzeVO);

			}else {
				orderAnalyzeVO.setDingdanShuliang(orderAnalyzeVO.getDingdanShuliang() + 1);
				if (ObjectUtils.equals(order.getIsNew(), 1)) {
					// 新用户下单数
					orderAnalyzeVO.setNewUserNum(orderAnalyzeVO.getNewUserNum() + 1);
				}else {
					// 老用户下单数
					orderAnalyzeVO.setOldUserNum(orderAnalyzeVO.getOldUserNum() + 1);
				}
				// 添加到map
				map.put(orderday, orderAnalyzeVO);
			}
		}

		// 根据日期补全数据
		for (int j = 0; j < dateI.length; j++) {
			OrderAnalyzeVO analyzeVO = map.get(dateI[j]);
			if (analyzeVO == null) {
				analyzeVO = new OrderAnalyzeVO(dateI[j], 0, 0, 0, 0, 0, 0, 0, 0, 0);
				map.put(dateI[j], analyzeVO);
			}
		}

		// 数据列表--返回列表使用
		List<OrderAnalyzeVO> analyzeVOs = new ArrayList<OrderAnalyzeVO>();
		map.forEach((k,v) ->{
			analyzeVOs.add(v);
		});

		// 准备下载的文件名
		String filename = "UserDataExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("用户数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"日期","新用户下单数","老用户下单数"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 3; j++) {
			sheet.setColumnWidth(j,26*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= analyzeVOs.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 3; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(analyzeVOs.get(i-1).getRiqi());
					break;
				case 1:
					cell.setCellValue(analyzeVOs.get(i-1).getNewUserNum());
					break;
				case 2:
					cell.setCellValue(analyzeVOs.get(i-1).getOldUserNum());
					break;

				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出用户数据execl");
		return;
	}

	/**
	 * 总后台导出骑手人效数据execl
	 */
	@RequestMapping("getHumanDataExecl.json")
	public void getHumanDataExecl(HttpServletRequest request, HttpServletResponse response,
			@RequestParam(value = "startTime", required = false, defaultValue = "0") int startTime,
			@RequestParam(value = "endTime", required = false, defaultValue = "0") int endTime) throws Exception {
		// 日期天数, 如果没有选择时间筛选条件 默认三十天
		int day = 30;
		if (startTime > 0 && endTime > 0) {
			String startday = DateUtil.intToString(startTime, "yyyyMMdd");
			String endday = DateUtil.intToString(endTime, "yyyyMMdd");
			day = AppUtil.daysBetween(startday, endday, "yyyyMMdd");
			// 获取天数
			day = day + 1;
			endTime = endTime + 86400;
		}else {
			endTime = DateUtil.getDateZeroTime(DateUtil.timeForUnix10());// 当日零点的时间戳 
			startTime = endTime - 30 * 86400;// 一月之前时间戳
		}
		// 日期数组
		int[] dateI = new int[day];
		Integer firstday = Integer.valueOf(DateUtil.intToString(startTime, "yyyyMMdd"));
		dateI[0] = firstday;
		for (int j = 1; j < dateI.length; j++) {
			// 在上个元素天数的基础上加一天
			dateI[j] = dateI[j-1] + 1;
			// 当前日期
			String dateString = dateI[j] + "";
			// 判断当前日是否是当前月最后一日
			int monthLastDay = AppUtil.getMonthLastDay(DateUtil.StringToInt(dateI[j-1] + "", "yyyyMMdd"), "yyyyMMdd");
			if (dateI[j] - monthLastDay == 1) {
				// 判断当前月是否是当前年最后一月
				if (Objects.equals(dateString.substring(4, 6), "12")) {
					// 跨年跨月
					Integer substring = Integer.valueOf(dateString.substring(0, 4));
					String yearFirst = AppUtil.getYearFirst(substring + 1,"yyyyMMdd");
					dateI[j] = Integer.valueOf(yearFirst);
				}else {
					// 只跨月不跨年
					String firstDayOfNextMonth = AppUtil.getFirstDayOfNextMonth(dateI[j-1] + "", "yyyyMMdd");
					dateI[j] = Integer.valueOf(firstDayOfNextMonth);
				}
			}
		}

		// 获取在这个时间段的订单
		String ordernow = "SELECT * FROM shop_order WHERE addtime > " + startTime + " AND  addtime < " + endTime ;
		List<Order> list = sqlService.findBySqlQuery(ordernow, Order.class);
		//计算骑手数量
		int count = sqlService.count("laji_qishou"," WHERE state = " + Qishou.STATE_NORMAL);
		// 日期-数据对象
		Map<Integer, HumanEffectAnalyzeVO> map = new TreeMap<Integer, HumanEffectAnalyzeVO>();		
		for (Order order : list) {
			// 根据日期进行数据分发
			Integer orderday = Integer.valueOf(DateUtil.intToString(order.getAddtime(), "yyyyMMdd"));
			// 某天的数据,用于列表
			HumanEffectAnalyzeVO humanEffectAnalyzeVO = map.get(orderday);
			if (humanEffectAnalyzeVO == null) {
				humanEffectAnalyzeVO = new HumanEffectAnalyzeVO();
				//接单数量
				humanEffectAnalyzeVO.setRenjunjiedan(1);
				//支付金额（单位 分）
				humanEffectAnalyzeVO.setRenjunMoney(order.getPayMoney());
				//回收重量(单位 g)
				humanEffectAnalyzeVO.setRenjunWeight(order.getAllWeight());
				//订单完成时间（单位 分）
				if((ObjectUtils.equals(order.getState(), Order.STATE_FINISH) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED)) && order.getPayTime() != null && order.getAddtime() != null){
					humanEffectAnalyzeVO.setPingjunTime( (order.getPayTime() - order.getAddtime() ) / 60);
				}else {
					humanEffectAnalyzeVO.setPingjunTime(0);
				}
				//异常订单数
				if(ObjectUtils.equals(order.getState(), Order.STATE_DAIJIEDAN) || (order.getReserveEndTime() < DateUtil.timeForUnix10() && ObjectUtils.equals(order.getState(), Order.STATE_DAIQUHUO))){
					humanEffectAnalyzeVO.setRenjunyichang(1);
				}else {
					humanEffectAnalyzeVO.setRenjunyichang(0);
				}
				// 日期
				humanEffectAnalyzeVO.setRiqi(Integer.valueOf(DateUtil.intToString(order.getAddtime(), "yyyyMMdd")));
				// 添加到map
				map.put(orderday, humanEffectAnalyzeVO);

			}else {
				//接单
				humanEffectAnalyzeVO.setRenjunjiedan(humanEffectAnalyzeVO.getRenjunjiedan() + 1);
				//金额
				humanEffectAnalyzeVO.setRenjunMoney(humanEffectAnalyzeVO.getRenjunMoney() + order.getPayMoney());
				//重量
				humanEffectAnalyzeVO.setRenjunWeight(humanEffectAnalyzeVO.getRenjunWeight() + order.getAllWeight());
				//时间（单位 分）
				if((ObjectUtils.equals(order.getState(), Order.STATE_FINISH) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED)) && order.getPayTime() != null && order.getAddtime() != null){
					humanEffectAnalyzeVO.setPingjunTime(humanEffectAnalyzeVO.getPingjunTime() + (order.getPayTime() - order.getAddtime()) / 60);
				}else {
					humanEffectAnalyzeVO.setPingjunTime(humanEffectAnalyzeVO.getPingjunTime());
				}

				//异常订单数
				if(ObjectUtils.equals(order.getState(), Order.STATE_DAIJIEDAN) || (order.getReserveEndTime() < DateUtil.timeForUnix10() && ObjectUtils.equals(order.getState(), Order.STATE_DAIQUHUO))){
					humanEffectAnalyzeVO.setRenjunyichang(humanEffectAnalyzeVO.getRenjunyichang() + 1);
				}else {
					humanEffectAnalyzeVO.setRenjunyichang(humanEffectAnalyzeVO.getRenjunyichang());
				}
				// 添加到map
				map.put(orderday, humanEffectAnalyzeVO);

			}
		}
		// 根据日期补全数据
		for (int j = 0; j < dateI.length; j++) {
			HumanEffectAnalyzeVO humanEffectAnalyzeVO = map.get(dateI[j]);
			if (humanEffectAnalyzeVO == null) {
				humanEffectAnalyzeVO = new HumanEffectAnalyzeVO(dateI[j], 0, 0, 0, 0, 0);
				map.put(dateI[j], humanEffectAnalyzeVO);
			}
		}

		// 数据列表--返回列表使用

		List<HumanEffectAnalyzeVO> humanEffectAnalyzeVOs = new ArrayList<HumanEffectAnalyzeVO>();
		for (HumanEffectAnalyzeVO hAnalyzeVO : map.values()) {
			humanEffectAnalyzeVOs.add(hAnalyzeVO);
		}
		// 准备下载的文件名
		String filename = "HumanDataExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("骑手人效数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"日期","人均接单数量","人均回收金额（元）","人均回收重量（kg）","平均订单完成时间（分钟）","人均订单异常量"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 6; j++) {
			sheet.setColumnWidth(j,36*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= humanEffectAnalyzeVOs.size(); i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 10; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(humanEffectAnalyzeVOs.get(i-1).getRiqi());
					break;
				case 1:
					double renjunjiedan = (double)humanEffectAnalyzeVOs.get(i-1).getRenjunjiedan();
					cell.setCellValue(DoubleUtil.doubleSplit((double)renjunjiedan/count,2));
					break;
				case 2:
					double renjunMoney = (double)humanEffectAnalyzeVOs.get(i-1).getRenjunMoney() / 100;
					cell.setCellValue(DoubleUtil.doubleSplit((double)renjunMoney/count,2));
					break;
				case 3:
					double renjunWeight = (double)humanEffectAnalyzeVOs.get(i-1).getRenjunWeight() / 1000;
					cell.setCellValue(DoubleUtil.doubleSplit((double)renjunWeight/count,2));
					break;
				case 4:
					double pingjunTime = (double)humanEffectAnalyzeVOs.get(i-1).getPingjunTime();
					cell.setCellValue(DoubleUtil.doubleSplit((double)pingjunTime/count,2));
					break;
				case 5:
					double renjunyichang = (double)humanEffectAnalyzeVOs.get(i-1).getRenjunyichang();
					cell.setCellValue(DoubleUtil.doubleSplit((double)renjunyichang/count,2));
					break;
				default:
					break;
				}
			}
		}

		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出骑手人效数据execl");
		return;
	}


	/**
	 * 总后台导出骑手考勤数据execl
	 */
	@RequestMapping("getRiderDataExecl.json")
	public void getRiderDataExecl(HttpServletRequest request, HttpServletResponse response,
			@RequestParam(value = "startTime", required = false, defaultValue = "0") int startTime,
			@RequestParam(value = "endTime", required = false, defaultValue = "0") int endTime) throws Exception {
		Sql sql = new Sql(request);

		// 日期天数, 如果没有选择时间筛选条件 默认是本月的
		if (startTime > 0 && endTime > 0) {
			int parseInt = (endTime - startTime) / (3600 * 24);

		}else {
			endTime = DateUtil.timeForUnix10();// 当前时间戳
			startTime = (int)AppUtil.getMonthBegin4Linux();// 本月一日零点
		}
		//获取该用户可以查看的骑手并分页
		int count = sqlService.count("laji_qishou", sql.getWhere());
		Page page = new Page(count, 15, request);
		sql.setSelectFromAndPage("SELECT * FROM laji_qishou ", page);
		sql.setDefaultOrderBy("id ASC");
		List<Qishou> qishouList = sqlService.findBySql(sql,Qishou.class);
		// 放骑手用户id
		StringBuffer buffer = new StringBuffer("0");

		// 数据容器 k: 骑手id,v: RiderAnalyzeVO对象
		Map<Integer, RiderAnalyzeVO> map = new HashMap<Integer, RiderAnalyzeVO>();
		List<RiderAnalyzeVO> list = new ArrayList<RiderAnalyzeVO>();
		for (Qishou qishou : qishouList) {
			buffer.append("," + qishou.getId());
			RiderAnalyzeVO analyze = new RiderAnalyzeVO(qishou.getName(), 0, 0.0);
			map.put(qishou.getId(), analyze);
			list.add(analyze);
		}

		//查询骑手请假记录表内已通过审核且函数处理完成的请假记录
		String qijiaFinish = "SELECT * FROM laji_qishou_leave_record WHERE state = " 
				+ QishouLeaveRecord.STATE_FINISH + " AND create_time > " + startTime + " AND create_time < " + endTime + " AND qishou_userid IN(" + buffer + ")";
		List<QishouLeaveRecord> finishList = sqlService.findBySqlQuery(qijiaFinish,QishouLeaveRecord.class);

		//遍历骑手请假记录表
		for (QishouLeaveRecord qishouLeaveRecord : finishList) {
			RiderAnalyzeVO analyzeVO = map.get(qishouLeaveRecord.getQishouUserid());
			if (analyzeVO == null) {
				analyzeVO = new RiderAnalyzeVO();
				analyzeVO.setNumSum(0);
				analyzeVO.setTimeSum(0.0);
				analyzeVO.setQishouName(qishouLeaveRecord.getQishouUsername());
				//计算请假时长,秒数换成小时
				//请假结束时间比筛选结束晚 但是请假开始时间在筛选时间段内
				if(qishouLeaveRecord.getEndTime() >= endTime && qishouLeaveRecord.getStartTime() > startTime && qishouLeaveRecord.getStartTime() < endTime) {
					analyzeVO.setTimeSum( DoubleUtil.doubleSplit((double)( endTime - qishouLeaveRecord.getStartTime() ) / 3600,2) );
					analyzeVO.setNumSum(analyzeVO.getNumSum() + 1);
				}
				//如果请假开始和结束时间在筛选时间范围内
				if (qishouLeaveRecord.getEndTime() <= endTime && qishouLeaveRecord.getEndTime() > startTime && qishouLeaveRecord.getStartTime() >= startTime && qishouLeaveRecord.getStartTime() < endTime) {
					analyzeVO.setNumSum(analyzeVO.getNumSum() + 1);
					analyzeVO.setTimeSum( DoubleUtil.doubleSplit( (double)( qishouLeaveRecord.getEndTime() - qishouLeaveRecord.getStartTime() ) / 3600,2) );
				}
				//如果请假结束时间在筛选时间之内，但是请假开始时间比筛选开始时间早
				if (qishouLeaveRecord.getEndTime() > startTime && qishouLeaveRecord.getEndTime() <= endTime && qishouLeaveRecord.getStartTime() <= startTime) {
					analyzeVO.setTimeSum( DoubleUtil.doubleSplit((double)( qishouLeaveRecord.getEndTime() - startTime  ) / 3600,2) );
					analyzeVO.setNumSum(analyzeVO.getNumSum() + 1);
				}
				//如果请假开始和结束时间包含了筛选的时段
				if (qishouLeaveRecord.getEndTime() > endTime && qishouLeaveRecord.getStartTime() < startTime) {
					analyzeVO.setTimeSum( DoubleUtil.doubleSplit((double)( endTime - startTime  ) / 3600,2) );
					analyzeVO.setNumSum(analyzeVO.getNumSum() + 1);
				}

				list.add(analyzeVO);
			}else {
				// analyzeVO已经存在了,只需要取出之前的值来在加上新的值即可
				//请假结束时间比筛选结束晚 但是请假开始时间在筛选时间段内
				if(qishouLeaveRecord.getEndTime() >= endTime && qishouLeaveRecord.getStartTime() > startTime && qishouLeaveRecord.getStartTime() < endTime) {
					analyzeVO.setTimeSum(analyzeVO.getTimeSum() + DoubleUtil.doubleSplit((double)( endTime - qishouLeaveRecord.getStartTime() ) / 3600,2));
					analyzeVO.setNumSum(analyzeVO.getNumSum() + 1);
				}
				//如果请假开始和结束时间在筛选时间范围内
				if (qishouLeaveRecord.getEndTime() <= endTime && qishouLeaveRecord.getEndTime() > startTime && qishouLeaveRecord.getStartTime() >= startTime && qishouLeaveRecord.getStartTime() < endTime) {
					analyzeVO.setNumSum(analyzeVO.getNumSum() + 1);
					analyzeVO.setTimeSum(analyzeVO.getTimeSum() + ( DoubleUtil.doubleSplit( (double)( qishouLeaveRecord.getEndTime() - qishouLeaveRecord.getStartTime() ) / 3600,2) ));
				}
				//如果请假结束时间在筛选时间之内，但是请假开始时间比筛选开始时间早
				if (qishouLeaveRecord.getEndTime() > startTime && qishouLeaveRecord.getEndTime() <= endTime && qishouLeaveRecord.getStartTime() <= startTime) {
					analyzeVO.setTimeSum(analyzeVO.getTimeSum() + DoubleUtil.doubleSplit((double)( qishouLeaveRecord.getEndTime() - startTime  ) / 3600,2) );
					analyzeVO.setNumSum(analyzeVO.getNumSum() + 1);
				}
				//如果请假开始和结束时间包含了筛选的时段
				if (qishouLeaveRecord.getEndTime() > endTime && qishouLeaveRecord.getStartTime() < startTime) {
					analyzeVO.setTimeSum(analyzeVO.getTimeSum() + DoubleUtil.doubleSplit((double)( endTime - startTime  ) / 3600,2) );
					analyzeVO.setNumSum(analyzeVO.getNumSum() + 1);
				}
			}
		}

		// 准备下载的文件名
		String filename = "RiderDataExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("骑手考勤数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"骑手名字","请假次数","请假时长","操作"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 4; j++) {
			sheet.setColumnWidth(j,26*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= list.size() ; i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 4; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(list.get(i-1).getQishouName());
					break;
				case 1:
					cell.setCellValue(list.get(i-1).getNumSum());
					break;
				case 2:
					cell.setCellValue(list.get(i-1).getTimeSum());
					break;
				case 3:
					if(list.get(i-1).getNumSum() > 0 ) {
						cell.setCellValue("缺勤");
					}else {
						cell.setCellValue("全勤");
					}
					break;
				default:
					break;
				}
			}
		}
		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出骑手考勤数据execl");
		return;
	}

	/**
	 * 总后台导出骑手工作量数据execl
	 */
	@RequestMapping("getWorkDataExecl.json")
	public void getWorkDataExecl(HttpServletRequest request, HttpServletResponse response,
			@RequestParam(value = "startTime", required = false, defaultValue = "0") int startTime,
			@RequestParam(value = "endTime", required = false, defaultValue = "0") int endTime) throws Exception {
		// 日期天数, 如果没有选择时间筛选条件 默认三十天
		int day = 30;
		if (startTime > 0 && endTime > 0) {
			String startday = DateUtil.intToString(startTime, "yyyyMMdd");
			String endday = DateUtil.intToString(endTime, "yyyyMMdd");
			day = AppUtil.daysBetween(startday, endday, "yyyyMMdd");

			day = day + 1;
			endTime = endTime + 86400;
		}else {
			endTime = DateUtil.getDateZeroTime(DateUtil.timeForUnix10());// 当日零点的时间戳 
			startTime = endTime - 30 * 86400;// 一月之前时间戳
		}
		// 日期数组
		int[] dateI = new int[day];
		Integer firstday = Integer.valueOf(DateUtil.intToString(startTime, "yyyyMMdd"));
		dateI[0] = firstday;
		for (int j = 1; j < dateI.length; j++) {
			// 在上个元素天数的基础上加一天
			dateI[j] = dateI[j-1] + 1;
			// 当前日期
			String dateString = dateI[j] + "";
			// 判断当前日是否是当前月最后一日
			int monthLastDay = AppUtil.getMonthLastDay(DateUtil.StringToInt(dateI[j-1] + "", "yyyyMMdd"), "yyyyMMdd");
			if (dateI[j] - monthLastDay == 1) {
				// 判断当前月是否是当前年最后一月
				if (Objects.equals(dateString.substring(4, 6), "12")) {
					// 跨年跨月
					Integer substring = Integer.valueOf(dateString.substring(0, 4));
					String yearFirst = AppUtil.getYearFirst(substring + 1,"yyyyMMdd");
					dateI[j] = Integer.valueOf(yearFirst);
				}else {
					// 只跨月不跨年
					String firstDayOfNextMonth = AppUtil.getFirstDayOfNextMonth(dateI[j-1] + "", "yyyyMMdd");
					dateI[j] = Integer.valueOf(firstDayOfNextMonth);
				}
			}
		}

		// 获取在这个时间段的订单
		String ordernow = "SELECT * FROM shop_order WHERE addtime > " + startTime + " AND  addtime < " + endTime;
		List<Order> list = sqlService.findBySqlQuery(ordernow, Order.class);

		//数据容器 k: 日期 , v: 数据容器(k: 骑手id,v: WorkAnalyzeVO对象)
		Map<Integer, Map<Integer, WorkAnalyzeVO>> waicengriqimap = new TreeMap<Integer, Map<Integer,WorkAnalyzeVO>>();

		//遍历外层map get日期的key
		for (Order order : list) {
			//取出订单日期的时间 判断容器内是否有这个日期的数据
			Integer orderday = Integer.valueOf(DateUtil.intToString(order.getAddtime(), "yyyyMMdd"));
			Map<Integer, WorkAnalyzeVO> newmap = waicengriqimap.get(orderday);
			//如果没有 就新建一条数据
			if (newmap == null) {
				//newmap为空 那么数据必然为空 直接新建赋值  数据容器 k: 骑手id,v: WorkAnalyzeVO对象
				newmap = new HashMap<Integer, WorkAnalyzeVO>();
				WorkAnalyzeVO workAnalyzeVO = new WorkAnalyzeVO();
				//日期
				workAnalyzeVO.setRiqi(orderday);
				workAnalyzeVO.setQishouName(order.getQishouName());
				workAnalyzeVO.setDingdanShuliang(1);
				workAnalyzeVO.setWeight(order.getAllWeight());
				workAnalyzeVO.setMoney(order.getPayMoney());
				//总完成数量
				if (ObjectUtils.equals(order.getState(),Order.STATE_FINISH) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED) ) {
					workAnalyzeVO.setDoneNum(1);
				}else {
					workAnalyzeVO.setDoneNum(0);
				}
				//总未完成数量
				Integer noNum = workAnalyzeVO.getDingdanShuliang() - workAnalyzeVO.getDoneNum() < 0 ? 0 : workAnalyzeVO.getDingdanShuliang() - workAnalyzeVO.getDoneNum();
				workAnalyzeVO.setWeiwanchengNum(noNum);
				//订单总取消数量
				if (ObjectUtils.equals(order.getState(),Order.STATE_CANCEL)) {
					workAnalyzeVO.setQuxiaoNum(1);
				}else {
					workAnalyzeVO.setQuxiaoNum(0);
				}
				//订单异常数量
				if (ObjectUtils.equals(order.getState(), Order.STATE_DAIJIEDAN) || (order.getReserveEndTime() < DateUtil.timeForUnix10() && ObjectUtils.equals(order.getState(), Order.STATE_DAIQUHUO))) {
					workAnalyzeVO.setYqwanchengNum(1);
				}else {
					workAnalyzeVO.setYqwanchengNum(0);
				}
				//订单平均时间 待添加(1.取出订单中，已完成的订单 2.计算时间差赋值)
				if((ObjectUtils.equals(order.getState(),Order.STATE_FINISH ) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED)) && order.getPayTime() != null && order.getPayTime() > order.getAddtime()) {
					workAnalyzeVO.setPingjunTime(order.getPayTime() - order.getAddtime());
				}else{
					workAnalyzeVO.setPingjunTime(0);			
				}
				//将数据填充进内层容器map
				newmap.put(order.getQishouId(), workAnalyzeVO);
				//将内层map加上日期填充进外层map
				waicengriqimap.put(orderday,newmap);
			}else {
				//如果该日期下newmap内已经有数据 那取出内层容器的key 判断内层容器是否有对应的数据
				WorkAnalyzeVO workAnalyzeVO = newmap.get(order.getQishouId());
				//如果没有当前k：骑手id 对应的数据 新建
				if(workAnalyzeVO == null) {
					workAnalyzeVO = new WorkAnalyzeVO();
					//日期
					workAnalyzeVO.setRiqi(orderday);
					workAnalyzeVO.setQishouName(order.getQishouName());
					workAnalyzeVO.setDingdanShuliang(1);
					workAnalyzeVO.setWeight(order.getAllWeight());
					workAnalyzeVO.setMoney(order.getPayMoney());
					//总完成数量
					if (ObjectUtils.equals(order.getState(),Order.STATE_FINISH) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED) ) {
						workAnalyzeVO.setDoneNum(1);
					}else {
						workAnalyzeVO.setDoneNum(0);
					}
					//总未完成数量
					Integer noNum = workAnalyzeVO.getDingdanShuliang() - workAnalyzeVO.getDoneNum() < 0 ? 0 : workAnalyzeVO.getDingdanShuliang() - workAnalyzeVO.getDoneNum();
					workAnalyzeVO.setWeiwanchengNum(noNum);
					//订单总取消数量
					if (ObjectUtils.equals(order.getState(),Order.STATE_CANCEL)) {
						workAnalyzeVO.setQuxiaoNum(1);
					}else {
						workAnalyzeVO.setQuxiaoNum(0);
					}
					//订单异常数量
					if (ObjectUtils.equals(order.getState(), Order.STATE_DAIJIEDAN) || (order.getReserveEndTime() < DateUtil.timeForUnix10() && ObjectUtils.equals(order.getState(), Order.STATE_DAIQUHUO))) {
						workAnalyzeVO.setYqwanchengNum(1);
					}else {
						workAnalyzeVO.setYqwanchengNum(0);
					}
					//订单平均时间 待添加(1.取出订单中，已完成的订单 2.计算时间差赋值)
					if((ObjectUtils.equals(order.getState(),Order.STATE_FINISH ) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED)) && order.getPayTime() != null && order.getPayTime() > order.getAddtime()) {
						workAnalyzeVO.setPingjunTime(order.getPayTime() - order.getAddtime());
					}else{
						workAnalyzeVO.setPingjunTime(0);			
					}
					//将这条数据添加进内层容器中
					newmap.put(order.getQishouId(),workAnalyzeVO);
					//将内层map加上日期填充进外层map
					waicengriqimap.put(orderday,newmap);
				}else {
					workAnalyzeVO.setDingdanShuliang(workAnalyzeVO.getDingdanShuliang() + 1);
					workAnalyzeVO.setWeight(workAnalyzeVO.getWeight() + order.getAllWeight());
					workAnalyzeVO.setMoney(workAnalyzeVO.getMoney() + order.getPayMoney());
					//总完成数量
					if (ObjectUtils.equals(order.getState(),Order.STATE_FINISH) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED) ) {
						workAnalyzeVO.setDoneNum(workAnalyzeVO.getDoneNum() + 1);
					}
					//总未完成数量
					Integer noNum = workAnalyzeVO.getDingdanShuliang() - workAnalyzeVO.getDoneNum() < 0 ? 0 : workAnalyzeVO.getDingdanShuliang() - workAnalyzeVO.getDoneNum();
					workAnalyzeVO.setWeiwanchengNum(noNum);
					//订单总取消数量
					if (ObjectUtils.equals(order.getState(),Order.STATE_CANCEL)) {
						workAnalyzeVO.setQuxiaoNum(workAnalyzeVO.getQuxiaoNum() + 1);
					}
					//订单异常数量
					if (ObjectUtils.equals(order.getState(), Order.STATE_DAIJIEDAN) || (order.getReserveEndTime() < DateUtil.timeForUnix10() && ObjectUtils.equals(order.getState(), Order.STATE_DAIQUHUO))) {
						workAnalyzeVO.setYqwanchengNum(workAnalyzeVO.getYqwanchengNum() + 1);
					}
					//平均时间
					if((ObjectUtils.equals(order.getState(),Order.STATE_FINISH ) || ObjectUtils.equals(order.getState(), Order.STATE_EVALUATED)) && order.getPayTime() != null && order.getPayTime() > order.getAddtime()) {
						int chaNum = order.getPayTime() - order.getAddtime();
						workAnalyzeVO.setPingjunTime(workAnalyzeVO.getPingjunTime() + chaNum);
					}
					//将这条数据添加进内层容器中
					newmap.put(order.getQishouId(),workAnalyzeVO);
					//将内层map加上日期填充进外层map
					waicengriqimap.put(orderday,newmap);
				}	
			}
		}
		// 拿到日期-骑手的数据,列表展示用
		List<WorkAnalyzeVO> workAnalyzeVOs = new ArrayList<WorkAnalyzeVO>();
		// 按日期获取列表
		Map<Integer, WorkAnalyzePicVO> map = new TreeMap<Integer, WorkAnalyzePicVO>();
		waicengriqimap.forEach((riqi,neiMap) -> {
			WorkAnalyzePicVO analyzeVO = new WorkAnalyzePicVO(0, 0, 0, 0, 0, 0, 0.0, 0);
			analyzeVO.setRiqi(riqi);
			neiMap.forEach((qishouId,wAnalyzeVO) -> {
				workAnalyzeVOs.add(wAnalyzeVO);
				analyzeVO.setDingdanShuliang(analyzeVO.getDingdanShuliang() + wAnalyzeVO.getDingdanShuliang());				
				analyzeVO.setDoneNum(analyzeVO.getDoneNum() + wAnalyzeVO.getDoneNum());
				analyzeVO.setMoney(analyzeVO.getMoney() + wAnalyzeVO.getMoney());
				// 平均时间
				analyzeVO.setPingjunTime(analyzeVO.getPingjunTime() + wAnalyzeVO.getPingjunTime());
				analyzeVO.setQuxiaoNum(analyzeVO.getQuxiaoNum() + wAnalyzeVO.getQuxiaoNum());
				analyzeVO.setWeight(analyzeVO.getWeight() + wAnalyzeVO.getWeight());
				analyzeVO.setWeiwanchengNum(analyzeVO.getWeiwanchengNum() + wAnalyzeVO.getWeiwanchengNum());
				analyzeVO.setYqwanchengNum(analyzeVO.getYqwanchengNum() + wAnalyzeVO.getYqwanchengNum());
			});
			map.put(riqi, analyzeVO);
		});

		for (int j = 0; j < dateI.length; j++) {
			WorkAnalyzePicVO workAnalyzePicVO = map.get(dateI[j]);
			if (workAnalyzePicVO == null) {
				workAnalyzePicVO = new WorkAnalyzePicVO(0, 0, 0, 0, 0, 0, 0.0, 0);
				workAnalyzePicVO.setRiqi(dateI[j]);
				map.put(dateI[j], workAnalyzePicVO);
			}
		}


		// 准备下载的文件名
		String filename = "WorkDataExecl.xlsx";
		response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
		response.setHeader("Content-disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");

		// 1.创建一个Excel文件(内存中)
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 2.创建一张表
		Sheet sheet = wb.createSheet("骑手工作量数据表");

		// 创建表头
		Row headRow = sheet.createRow(0);
		String[] strings = {"日期","骑手名字","订单总数量","完成订单总重量(kg)","总完成数量","总未完成数量","订单总取消数量","回收总金额(元)","平均订单完成时间","异常订单数量"};
		headRow.setHeightInPoints(36.25f);

		// 设置表头样式
		CellStyle headStyle = wb.createCellStyle();
		headStyle.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
		//8.创建字体对象
		Font font = wb.createFont();
		//9.设置字体和其大小及效果
		font.setFontName("黑体");
		font.setFontHeightInPoints((short)18);
		font.setBold(true); //加粗
		headStyle.setFont(font);

		for (int j = 0; j < 10; j++) {
			sheet.setColumnWidth(j,30*256); //(列的索引,列宽*256(理解为固定写法))
			Cell headCell = headRow.createCell(j);
			headCell.setCellValue(strings[j]);
			//11.为单元格应用样式
			headCell.setCellStyle(headStyle);
		}

		// 创建表格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);        //横向居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中

		// 3.创建表格行
		for (int i = 1; i <= workAnalyzeVOs.size() ; i++) {
			Row row = sheet.createRow(i);
			row.setHeightInPoints(26.25f);

			for (int j = 0; j < 10; j++) {
				Cell cell = row.createCell(j);
				// 为单元格应用样式
				cell.setCellStyle(style);
				// 5.格子中加数据
				switch (j) {
				case 0:
					cell.setCellValue(workAnalyzeVOs.get(i-1).getRiqi());
					break;
				case 1:
					cell.setCellValue(workAnalyzeVOs.get(i-1).getQishouName());
					break;
				case 2:
					cell.setCellValue(workAnalyzeVOs.get(i-1).getDingdanShuliang());
					break;
				case 3:
					cell.setCellValue(DoubleUtil.doubleSplit((double)workAnalyzeVOs.get(i-1).getWeight()/1000 ,2));
					break;
				case 4:
					cell.setCellValue(workAnalyzeVOs.get(i-1).getDoneNum());
					break;
				case 5:
					cell.setCellValue(workAnalyzeVOs.get(i-1).getWeiwanchengNum());
					break;
				case 6:
					cell.setCellValue(workAnalyzeVOs.get(i-1).getQuxiaoNum());
					break;
				case 7:
					cell.setCellValue(DoubleUtil.doubleSplit((double)workAnalyzeVOs.get(i-1).getMoney()/100,2));
					break;
				case 8:
					double pingjuntime = (double)workAnalyzeVOs.get(i-1).getPingjunTime()/60;
					Integer dingdanshuliang = workAnalyzeVOs.get(i-1).getDingdanShuliang();
					if( dingdanshuliang > 0) {
						cell.setCellValue(DoubleUtil.doubleSplit((double)pingjuntime/dingdanshuliang,2));
					}else {
						cell.setCellValue(DoubleUtil.doubleSplit((double)pingjuntime,2));
					}
					break;
				case 9:
					cell.setCellValue(workAnalyzeVOs.get(i-1).getYqwanchengNum());
					break;
				default:
					break;
				}
			}
		}
		//从内存中写出来
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();

		ActionLogUtil.insert(request,"总后台导出骑手工作量数据execl");
		return;
	}


}
